﻿
//TODO: Add connection string

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlClient;
using System.Data;

using eb = MyData.ExpressionBuilder.Model;
using MyData.Activities;


namespace MyDataTestProject
{
  public class SqlAzureSecurity
  {
    public bool Encrypt { get; set; }
    public bool TrustServerCertificate { get; set; }
  }

  public abstract class Schema
  {
    public string SchemaName { get; set; }
    public string Name { get; set; }
    public string DataType { get; set; }
    public int? DataSize { get; set; }

  }

  public class Column : Schema
  {
    public bool IsNullable { get; set; }
  }


  public class Parameter : Schema
  {
    public string Mode { get; set; }
  }

  [TestClass]
  public class SQLAzure
  {
    SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();

    public string ConnectionString()
    {
      LoginForm loginForm = new LoginForm();
      loginForm.DataAvailable += new LoginForm.ActivityFormEventHandler(loginForm_DataAvailable);
      loginForm.ShowDialog();

      return connStringBuilder.ToString();
    }

    void loginForm_DataAvailable(object sender, ActivityFormEventArgs e)
    {
      //<.database.windows.net>
      connStringBuilder.DataSource = @".\SQLExpress";
      //<databasename>
      connStringBuilder.InitialCatalog = "AdventureWorks";

      //NOTE: Integrated Security
      connStringBuilder.IntegratedSecurity = true;

      //Show the LoginFormActivity to get the  <userName> and <password>
      //SQL Azure: Get UserID and Password from LoginFormActivty

      //connStringBuilder.Encrypt = true;
      //connStringBuilder.TrustServerCertificate = false;
      //connStringBuilder.UserID = <userName>;
      //connStringBuilder.Password = <password>;
    }


    [TestMethod]
    public void IsConnected()
    {
      using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
      {
        conn.Open();

        Assert.IsTrue(conn.State == System.Data.ConnectionState.Open);
      }
    }

    //Activities only support views and procedures since they are desinged for business logic / controller workflows
    //Simple SQL Where and OrderBy will be supported; however it is assumed that complex Views and Procedures are developed
    //as T-SQL script. This promotes a better seperation of concerns.
    [TestMethod]
    public void ViewNames()
    {
      using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
      {
        conn.Open();

        List<string> views = (from r in conn.GetSchema("TABLES").AsEnumerable()
                              where r.Field<string>("TABLE_TYPE") == "VIEW"
                              select String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME"))).ToList<string>();

        Assert.IsTrue(views.Count > 0);
      }
    }

    [TestMethod]
    public void GetViewSchema()
    {
      string InArgument_ViewName = "Purchasing.vEmployee";

      string[] splitName = InArgument_ViewName.Split(new char[] { '.' });
      string nonSchemaName = splitName.Length > 0 ? splitName [1] : string.Empty;

      using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
      {
        conn.Open();


        List<Column> viewSchema = (from r in conn.GetSchema("Columns").AsEnumerable()
                                   where r.Field<string>("TABLE_NAME") == nonSchemaName
                                   orderby r.Field<int>("ORDINAL_POSITION")
                                   select new Column
                                   {
                                     SchemaName = r.Field<string>("table_schema"),
                                     Name = r.Field<string>("COLUMN_NAME"),
                                     DataType = r.Field<string>("DATA_TYPE"),
                                     DataSize = r.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                                     IsNullable = r.Field<string>("IS_NULLABLE") == "YES" ? true : false
                                   }
                                   ).ToList<Column>();

        Assert.IsTrue(viewSchema.Count > 0);
      }
    }

    [TestMethod]
    public void ProcedureNames()
    {
      using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
      {
        conn.Open();

        List<string> procedures = (from r in conn.GetSchema("PROCEDURES").AsEnumerable()
                                   where r.Field<string>("ROUTINE_TYPE") == "PROCEDURE"
                                   select String.Format("{0}.{1}", r.Field<string>("specific_schema"), r.Field<string>("ROUTINE_NAME")))
                              .ToList<string>();

        Assert.IsTrue(procedures.Count > 0);
      }
    }

    [TestMethod]
    public void ProcedureParameters()
    {
      string InArgument_ProcedureName = "uspGetBillOfMaterials";

      using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
      {
        conn.Open();

        List<Parameter> procedures = (from r in conn.GetSchema("ProcedureParameters").AsEnumerable()
                                      where r.Field<string>("SPECIFIC_NAME") == InArgument_ProcedureName
                                      orderby r.Field<int>("ORDINAL_POSITION")
                                      select new Parameter
                                      {
                                        SchemaName = r.Field<string>("specific_schema"),
                                        Name = r.Field<string>("parameter_name"),
                                        DataType = r.Field<string>("DATA_TYPE"),
                                        DataSize = r.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                                        Mode = r.Field<string>("parameter_mode")
                                      }
                                        ).ToList<Parameter>();

        Assert.IsTrue(procedures.Count > 0);
      }
    }

  }
}
